const dbconfig = require('../db/dbconfig.js');
const md5 = require('md5-node');
// 执行sql函数
execute = (sql,arr)=>{
    return new Promise((resolve,reject)=>{
        dbconfig.query(sql,arr,function(result){
            if(result.length>0||result.affectedRows>0){
                console.log(result);
                resolve(result)
            }else
                reject('执行失败')
        })
    })
}
// 查看是否存在
select = (key,val,form)=>{
    let sql = `select * from ${form} where ${key}=?`;
    let arr=[val];
    return  new Promise((resovle,reject)=>{
        dbconfig.query(sql,arr,function(result,err){
            if(result.length > 0){
                resovle(result[0])
                console.log(result);
            }else
            reject(false)
    })
     
    })  
}
// 获取表的所有信息
selectAll = (form)=>{
    let sql =`select * from ${form}`;
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,null,function(result,err){
             if(result.length > 0)
              resovle(result);
             else
              reject(false);
        })
    })
}
// 多表查询信息
selectAllMuti = (query,key,form1,form2)=>{
    let sql = ` select ${query} from ${form1},${form2} where ${form1}.${key}=${form2}.${key} `

    return new Promise((resolve,reject)=>{
        dbconfig.query(sql,null,function(result,err){
             if(result.length > 0)
             resolve(result);
             else
              reject(false);
        })
    })
}
// 分页查询
selectByPagination = (pagination,form)=>{
    let sql =`select * from ${form} limit ${pagination.pageSize} offset ${pagination.offset}`
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,null,function(result,err){
            if(result.length >0)
             resovle(result);
            else
             reject(false)
        })
    })
    
}
// 获取总记录数
getTotal = (form,where)=>{
    let sql = `select count(*)as total from ${form} `;
    if(where){
        sql += `where ${where.key}=${where.value}`
    }
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,null,function(result,err){
            if(result.length >0)
             resovle(result);
            else
             reject(false)
        })
    })
}
// 删除某条记录
remove = (query,key,form)=>{
    let sql = `delete from ${form} where ${key}=${query}`;
    return new Promise((resolve,reject)=>{
        dbconfig.query(sql,null,function(result,err){
            if(result.affectedRows > 0)
             resolve(true);
            else
             reject(false);
        })
    })
}
// ----------- 场地部分 ------------------------

getCourtBySid = (sid)=>{
    let sql = `select cid,cname,price,state,sname,startTime,endTime from stadium,court where stadium.sid=court.sid and court.sid=${sid}`;
    return new Promise((resolve,reject)=>{
        dbconfig.query(sql,null,function(result,err){
             if(result.length > 0){
             console.log(result);
             resolve(result);
             }
             else
              reject(false);
        })
    })
},
// 添加场地
addCourt = (data)=>{
    let arr = [data.cname,data.price,data.sid,data.state,data.startTime,data.endTime];
    let sql = `insert into court(cname,price,sid,state,startTime,endTime) values(?,?,?,?,?,?)`;
   

    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,arr,function(result){
            console.log(result);
            if(result.affectedRows > 0){
                resovle(true)
            }
            else
               reject(false)
        })
    })
}
// 编辑场地信息
editCourt = (data) =>{
    let arr = [data.cname,data.price,data.state,data.startTime,data.endTime,data.cid];
    let sql = `update court set cname=?,price=?,state=?,startTime=?,endTime=? where cid=?`;
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,arr,function(result){
            console.log(result);
            if(result.affectedRows > 0){
                resovle('场地信息已更新')
            }
            else
               reject('更新失败')
        })
    })
}
// 删除场地
removeCourt = (cid)=>{
    let sql = `delete from court where cid=${cid}`
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,null,function(result){
            if(result.affectedRows > 0){
                resovle('场地删除成功')
            }
            else
               reject('场地删除失败')
        })
    })
   
}
// 场地模糊查询
searchCourt = (qurey)=>{
            let sql  =  `SELECT * FROM stadium WHERE cname LIKE '%${qurey}%' 
            or price LIKE '%${qurey}%'
            or address LIKE '%${qurey}%'
            or cid LIKE '%${qurey}%'`

        return new Promise((resolve,reject)=>{
            dbconfig.query(sql,null,function(result){
            if(result.length > 0)
              resolve(result);
            else
              reject(false);
            })
        })
}
// --------------------------------------------
// -------------球馆部分-----------------------
// 添加球馆
addStadium = (data)=>{
    let sql = `insert into stadium(sname,address,phone,picUrl,intro) value(?,?,?,?,?)`;
    let arr = [data.sName,data.sAddress,data.sPhone,data.picUrl,data.intro];

     return new Promise((resolve,reject)=>{
         dbconfig.query(sql,arr,function(result){
             console.log(result);
             if(result.affectedRows > 0){
                 resolve(true)
             }else
                 reject(false)
         }) 
     })
}
// 更新球馆信息
updateStadium = (data)=>{
    let sql =`UPDATE stadium SET sname=?, 
              address=?,
              phone=?,
              picUrl=?,
              intro=?
              where sid=?`
    let arr = [data.sName,data.sAddress,data.sPhone,data.picUrl,data.intro,data.sid];
    return new Promise((resovle,reject)=>{
        dbconfig.query(sql,arr,function(result){
            if(result.affectedRows > 0){
                resovle(true)
            }else
                reject(false)
        })
    })
}   
// 球馆的模糊查询
searchStadium = (qurey) =>{
    let sql  =  `SELECT * FROM stadium WHERE sname LIKE '%${qurey}%' 
                                       or phone LIKE '%${qurey}%'
                                       or address LIKE '%${qurey}%'
                                       or sid LIKE '%${qurey}%'`
                                  
    return new Promise((resolve,reject)=>{
        dbconfig.query(sql,null,function(result){
          if(result.length > 0)
          resolve(result);
          else
            reject(false);
        })
    })
 }
// -------------------------------------------
// 订单部分
// 添加订单
addOrderList = (data)=>{
    let sql = `insert into order_list(cid,uid,order_date,start_time,end_time,state) value(?,?,?,?,?,?)`;
    let arr = [data.cid,data.uid,data.orderDate,data.startTime,data.endTime,1];
    console.log(data);
     return new Promise((resolve,reject)=>{
         dbconfig.query(sql,arr,function(result){
             console.log(result);
             if(result.affectedRows > 0){
                 resolve('订单已添加')
             }else
                 reject('订单添加失败')
         }) 
     })
}


// ------------------------------------------
module.exports ={
    select,
    selectAll,
    selectAllMuti,
    selectByPagination,
    execute,
    getTotal,
    remove,
    addCourt,
    getCourtBySid,
    removeCourt,
    editCourt,
    addStadium,
    updateStadium,
    searchStadium,

    addOrderList
}
    